/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import model.AuthLog;
import model.AuthLogEntry;
import model.User;
import model.UserGroup;

/**
 *
 * @author modesto
 */
public class AuthLogDAO extends DAO {
    
    public AuthLogDAO() throws ClassNotFoundException, SQLException {
        super();
    }
    
    public void add (AuthLogEntry ale) throws SQLException {
        String sql = "INSERT INTO authlog VALUES (NULL, ?, INET_ATON(?), ?)";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);       
        stmt.setTimestamp(1, ale.getTimestamp());
        stmt.setString(2, ale.getIp());
        stmt.setInt(3, ale.getUser().getId());
     
        stmt.executeUpdate();
        
        ResultSet rs = stmt.getGeneratedKeys();
        
        rs.first();
        
        ale.setId(rs.getInt(1));
    }
    
    
    public AuthLog getAll() throws SQLException {
        AuthLog authLog = new AuthLog();
        String sql = "SELECT * FROM authlog al INNER JOIN user u INNER JOIN usergroup ug ON al.user_id = u.id AND u.group_id = ug.id";
        
        PreparedStatement stmt = this.getConnection().prepareStatement(sql);
        
        ResultSet rs = stmt.executeQuery();
        
        while (rs.next()) {
            AuthLogEntry ale = new AuthLogEntry();
            User user = new User();
            UserGroup ug = new UserGroup();
            ug.setId(rs.getInt("ug.id"));
            ug.setName(rs.getString("ug.name"));
            user.setId(rs.getInt("u.id"));
            user.setName(rs.getString("u.name"));
            user.setLogin(rs.getString("u.login"));
            user.setPassword(rs.getString("u.password"));
            user.setEmail(rs.getString("u.email"));
            user.setUserGroup(ug);
            ale.setId(rs.getInt("al.id"));
            ale.setTimestamp(rs.getTimestamp("al.timestamp"));
            ale.setIp(rs.getString("al.ip"));
            ale.setUser(user);
            authLog.add(ale);
        }
        
        return authLog;
    }
}
